--    Program: 		CAPPOIMP_SUPPLIER_SITE.sql
--    Description:  Import PO Supplier Site information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;
alter trigger PO_VENDOR_SITES_CT1 disable;
alter trigger PO_VENDOR_SITES_CT2 disable;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_vendor_site_id			number;
   
   lv_counter				number;
   lv_counter1				number;
                    		                 		
   cursor cur_vendor_sites is
      select * from CRST_PO_VENDOR_SITES_ALL
      for update;

begin
   --dbms_output.put_line('entering loop');
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
     --lv_counter1 := 0;
     --lv_counter := 0;
   
   --dbms_output.put_line(TO_CHAR('entering loop'));
   
   for cur in cur_vendor_sites loop   	
     l_err_msg_tmp := '';
     

     --dbms_output.put_line(cur.VENDOR_SITE_ID);
     --dbms_output.put_line(TO_CHAR(lv_counter1));
     
     --lv_counter1 := lv_counter1 + 1;
     
     select PO_VENDOR_SITES_S.NEXTVAL 
     into l_vendor_site_id
     FROM DUAL;
     
     update CRST_PO_VENDOR_SITES_ALL
     set    vendor_site_id_n = l_vendor_site_id
     where  vendor_site_id = cur.VENDOR_SITE_ID;
         
     begin    
       -- import data
       insert into PO_VENDOR_SITES_ALL
       (
         VENDOR_SITE_ID                 ,
         LAST_UPDATE_DATE               ,
         LAST_UPDATED_BY                ,
         VENDOR_ID                      ,
         VENDOR_SITE_CODE               ,
         VENDOR_SITE_CODE_ALT           ,
         LAST_UPDATE_LOGIN              ,
         CREATION_DATE                  ,
         CREATED_BY                     ,
         PURCHASING_SITE_FLAG           ,
         RFQ_ONLY_SITE_FLAG             ,
         PAY_SITE_FLAG                  ,
         ATTENTION_AR_FLAG              ,
         ADDRESS_LINE1                  ,
         ADDRESS_LINES_ALT              ,
         ADDRESS_LINE2                  ,
         ADDRESS_LINE3                  ,
         CITY                           ,
         STATE                          ,
         ZIP                            ,
         PROVINCE                       ,
         COUNTRY                        ,
         AREA_CODE                      ,
         PHONE                          ,
         CUSTOMER_NUM                   ,
         SHIP_TO_LOCATION_ID            ,
         BILL_TO_LOCATION_ID            ,
         SHIP_VIA_LOOKUP_CODE           ,
         FREIGHT_TERMS_LOOKUP_CODE      ,
         FOB_LOOKUP_CODE                ,
         INACTIVE_DATE                  ,
         FAX                            ,
         FAX_AREA_CODE                  ,
         TELEX                          ,
         PAYMENT_METHOD_LOOKUP_CODE     ,
         BANK_ACCOUNT_NAME              ,
         BANK_ACCOUNT_NUM               ,
         BANK_NUM                       ,
         BANK_ACCOUNT_TYPE              ,
         TERMS_DATE_BASIS               ,
         CURRENT_CATALOG_NUM            ,
         VAT_CODE                       ,
         DISTRIBUTION_SET_ID            ,
         ACCTS_PAY_CODE_COMBINATION_ID  ,
         PREPAY_CODE_COMBINATION_ID     ,
         PAY_GROUP_LOOKUP_CODE          ,
         PAYMENT_PRIORITY               ,
         TERMS_ID                       ,
         INVOICE_AMOUNT_LIMIT           ,
         PAY_DATE_BASIS_LOOKUP_CODE     ,
         ALWAYS_TAKE_DISC_FLAG          ,
         INVOICE_CURRENCY_CODE          ,
         PAYMENT_CURRENCY_CODE          ,
         HOLD_ALL_PAYMENTS_FLAG         ,
         HOLD_FUTURE_PAYMENTS_FLAG      ,
         HOLD_REASON                    ,
         HOLD_UNMATCHED_INVOICES_FLAG   ,
         AP_TAX_ROUNDING_RULE           ,
         AUTO_TAX_CALC_FLAG             ,
         AUTO_TAX_CALC_OVERRIDE         ,
         AMOUNT_INCLUDES_TAX_FLAG       ,
         EXCLUSIVE_PAYMENT_FLAG         ,
         TAX_REPORTING_SITE_FLAG        ,
         ATTRIBUTE_CATEGORY             ,
         ATTRIBUTE1                     ,
         ATTRIBUTE2                     ,
         ATTRIBUTE3                     ,
         ATTRIBUTE4                     ,
         ATTRIBUTE5                     ,
         ATTRIBUTE6                     ,
         ATTRIBUTE7                     ,
         ATTRIBUTE8                     ,
         ATTRIBUTE9                     ,
         ATTRIBUTE10                    ,
         ATTRIBUTE11                    ,
         ATTRIBUTE12                    ,
         ATTRIBUTE13                    ,
         ATTRIBUTE14                    ,
         ATTRIBUTE15                    ,
         REQUEST_ID                     ,
         PROGRAM_APPLICATION_ID         ,
         PROGRAM_ID                     ,
         PROGRAM_UPDATE_DATE            ,
         VALIDATION_NUMBER              ,
         EXCLUDE_FREIGHT_FROM_DISCOUNT  ,
         VAT_REGISTRATION_NUM           ,
         OFFSET_VAT_CODE                ,
         ORG_ID                         ,
         CHECK_DIGITS                   ,
         BANK_NUMBER                    ,
         ADDRESS_LINE4                  ,
         COUNTY                         ,
         ADDRESS_STYLE                  ,
         LANGUAGE                       ,
         ALLOW_AWT_FLAG                 ,
         AWT_GROUP_ID                   ,
         GLOBAL_ATTRIBUTE1              ,
         GLOBAL_ATTRIBUTE2              ,
         GLOBAL_ATTRIBUTE3              ,
         GLOBAL_ATTRIBUTE4              ,
         GLOBAL_ATTRIBUTE5              ,
         GLOBAL_ATTRIBUTE6              ,
         GLOBAL_ATTRIBUTE7              ,
         GLOBAL_ATTRIBUTE8              ,
         GLOBAL_ATTRIBUTE9              ,
         GLOBAL_ATTRIBUTE10             ,
         GLOBAL_ATTRIBUTE11             ,
         GLOBAL_ATTRIBUTE12             ,
         GLOBAL_ATTRIBUTE13             ,
         GLOBAL_ATTRIBUTE14             ,
         GLOBAL_ATTRIBUTE15             ,
         GLOBAL_ATTRIBUTE16             ,
         GLOBAL_ATTRIBUTE17             ,
         GLOBAL_ATTRIBUTE18             ,
         GLOBAL_ATTRIBUTE19             ,
         GLOBAL_ATTRIBUTE20             ,
         GLOBAL_ATTRIBUTE_CATEGORY      ,
         EDI_TRANSACTION_HANDLING       ,
         EDI_ID_NUMBER                  ,
         EDI_PAYMENT_METHOD             ,
         EDI_PAYMENT_FORMAT             ,
         EDI_REMITTANCE_METHOD          ,
         BANK_CHARGE_BEARER             ,
         EDI_REMITTANCE_INSTRUCTION     ,
         BANK_BRANCH_TYPE               ,
         PAY_ON_CODE                    ,
         DEFAULT_PAY_SITE_ID            ,
         PAY_ON_RECEIPT_SUMMARY_CODE    ,
         TP_HEADER_ID                   ,
         ECE_TP_LOCATION_CODE           ,
         PCARD_SITE_FLAG                ,
         MATCH_OPTION                   ,
         COUNTRY_OF_ORIGIN_CODE         ,
         FUTURE_DATED_PAYMENT_CCID      ,
         CREATE_DEBIT_MEMO_FLAG         ,
         OFFSET_TAX_FLAG                ,
         SUPPLIER_NOTIF_METHOD          ,
         EMAIL_ADDRESS                  ,
         REMITTANCE_EMAIL               ,
         PRIMARY_PAY_SITE_FLAG
       )
       values
       (
         l_vendor_site_id                   ,
         cur.LAST_UPDATE_DATE               ,
         cur.LAST_UPDATED_BY				,
         CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
         cur.VENDOR_SITE_CODE               ,
         cur.VENDOR_SITE_CODE_ALT           ,
         cur.LAST_UPDATE_LOGIN              ,
         cur.CREATION_DATE                  ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.PURCHASING_SITE_FLAG           ,
         cur.RFQ_ONLY_SITE_FLAG             ,
         cur.PAY_SITE_FLAG                  ,
         cur.ATTENTION_AR_FLAG              ,
         cur.ADDRESS_LINE1                  ,
         cur.ADDRESS_LINES_ALT              ,
         cur.ADDRESS_LINE2                  ,
         cur.ADDRESS_LINE3                  ,
         cur.CITY                           ,
         cur.STATE                          ,
         cur.ZIP                            ,
         cur.PROVINCE                       ,
         cur.COUNTRY                        ,
         cur.AREA_CODE                      ,
         cur.PHONE                          ,
         cur.CUSTOMER_NUM                   ,
         CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_TO_LOCATION_ID),
         CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.BILL_TO_LOCATION_ID),
         cur.SHIP_VIA_LOOKUP_CODE           ,
         cur.FREIGHT_TERMS_LOOKUP_CODE      ,
         cur.FOB_LOOKUP_CODE                ,
         cur.INACTIVE_DATE                  ,
         cur.FAX                            ,
         cur.FAX_AREA_CODE                  ,
         cur.TELEX                          ,
         cur.PAYMENT_METHOD_LOOKUP_CODE     ,
         cur.BANK_ACCOUNT_NAME              ,
         cur.BANK_ACCOUNT_NUM               ,
         cur.BANK_NUM                       ,
         cur.BANK_ACCOUNT_TYPE              ,
         cur.TERMS_DATE_BASIS               ,
         cur.CURRENT_CATALOG_NUM            ,
         cur.VAT_CODE                       ,
         cur.DISTRIBUTION_SET_ID            ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.ACCTS_PAY_CODE_COMBINATION_ID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.PREPAY_CODE_COMBINATION_ID),
         cur.PAY_GROUP_LOOKUP_CODE          ,
         cur.PAYMENT_PRIORITY               ,
         cur.TERMS_ID                       ,
         cur.INVOICE_AMOUNT_LIMIT           ,
         cur.PAY_DATE_BASIS_LOOKUP_CODE     ,
         cur.ALWAYS_TAKE_DISC_FLAG          ,
         cur.INVOICE_CURRENCY_CODE          ,
         cur.PAYMENT_CURRENCY_CODE          ,
         cur.HOLD_ALL_PAYMENTS_FLAG         ,
         cur.HOLD_FUTURE_PAYMENTS_FLAG      ,
         cur.HOLD_REASON                    ,
         cur.HOLD_UNMATCHED_INVOICES_FLAG   ,
         cur.AP_TAX_ROUNDING_RULE           ,
         cur.AUTO_TAX_CALC_FLAG             ,
         cur.AUTO_TAX_CALC_OVERRIDE         ,
         cur.AMOUNT_INCLUDES_TAX_FLAG       ,
         cur.EXCLUSIVE_PAYMENT_FLAG         ,
         cur.TAX_REPORTING_SITE_FLAG        ,
         cur.ATTRIBUTE_CATEGORY             ,
         cur.ATTRIBUTE1                     ,
         cur.ATTRIBUTE2                     ,
         cur.ATTRIBUTE3                     ,
         cur.ATTRIBUTE4                     ,
         cur.ATTRIBUTE5                     ,
         cur.ATTRIBUTE6                     ,
         cur.ATTRIBUTE7                     ,
         cur.ATTRIBUTE8                     ,
         cur.ATTRIBUTE9                     ,
         cur.ATTRIBUTE10                    ,
         cur.ATTRIBUTE11                    ,
         cur.ATTRIBUTE12                    ,
         cur.ATTRIBUTE13                    ,
         cur.ATTRIBUTE14                    ,
         cur.ATTRIBUTE15                    ,
         cur.REQUEST_ID                     ,
         cur.PROGRAM_APPLICATION_ID         ,
         cur.PROGRAM_ID                     ,
         cur.PROGRAM_UPDATE_DATE            ,
         cur.VALIDATION_NUMBER              ,
         cur.EXCLUDE_FREIGHT_FROM_DISCOUNT  ,
         cur.VAT_REGISTRATION_NUM           ,
         cur.OFFSET_VAT_CODE                ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.CHECK_DIGITS                   ,
         cur.BANK_NUMBER                    ,
         cur.ADDRESS_LINE4                  ,
         cur.COUNTY                         ,
         cur.ADDRESS_STYLE                  ,
         cur.LANGUAGE                       ,
         cur.ALLOW_AWT_FLAG                 ,
         cur.AWT_GROUP_ID                   ,
         cur.GLOBAL_ATTRIBUTE1              ,
         cur.GLOBAL_ATTRIBUTE2              ,
         cur.GLOBAL_ATTRIBUTE3              ,
         cur.GLOBAL_ATTRIBUTE4              ,
         cur.GLOBAL_ATTRIBUTE5              ,
         cur.GLOBAL_ATTRIBUTE6              ,
         cur.GLOBAL_ATTRIBUTE7              ,
         cur.GLOBAL_ATTRIBUTE8              ,
         cur.GLOBAL_ATTRIBUTE9              ,
         cur.GLOBAL_ATTRIBUTE10             ,
         cur.GLOBAL_ATTRIBUTE11             ,
         cur.GLOBAL_ATTRIBUTE12             ,
         cur.GLOBAL_ATTRIBUTE13             ,
         cur.GLOBAL_ATTRIBUTE14             ,
         cur.GLOBAL_ATTRIBUTE15             ,
         cur.GLOBAL_ATTRIBUTE16             ,
         cur.GLOBAL_ATTRIBUTE17             ,
         cur.GLOBAL_ATTRIBUTE18             ,
         cur.GLOBAL_ATTRIBUTE19             ,
         cur.GLOBAL_ATTRIBUTE20             ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY      ,
         cur.EDI_TRANSACTION_HANDLING       ,
         cur.EDI_ID_NUMBER                  ,
         cur.EDI_PAYMENT_METHOD             ,
         cur.EDI_PAYMENT_FORMAT             ,
         cur.EDI_REMITTANCE_METHOD          ,
         cur.BANK_CHARGE_BEARER             ,
         cur.EDI_REMITTANCE_INSTRUCTION     ,
         cur.BANK_BRANCH_TYPE               ,
         cur.PAY_ON_CODE                    ,
         cur.DEFAULT_PAY_SITE_ID            ,
         cur.PAY_ON_RECEIPT_SUMMARY_CODE    ,
         cur.TP_HEADER_ID                   ,
         cur.ECE_TP_LOCATION_CODE           ,
         cur.PCARD_SITE_FLAG                ,
         cur.MATCH_OPTION                   ,
         cur.COUNTRY_OF_ORIGIN_CODE         ,
         cur.FUTURE_DATED_PAYMENT_CCID      ,
         cur.CREATE_DEBIT_MEMO_FLAG         ,
         cur.OFFSET_TAX_FLAG                ,
         cur.SUPPLIER_NOTIF_METHOD          ,
         cur.EMAIL_ADDRESS                  ,
         cur.REMITTANCE_EMAIL               ,
         cur.PRIMARY_PAY_SITE_FLAG         
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_VENDOR_SITE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

alter trigger PO_VENDOR_SITES_CT1 enable;
alter trigger PO_VENDOR_SITES_CT2 enable;

--exit
